Skip Headers

Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 25 Monitoring File Group and Tablespace Repositories

A file group repository can contain multiple file groups and multiple versions of a particular file group. A tablespace repository is a collection of tablespace sets in a file group repository. Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases. This chapter provides sample queries that you can use to monitor file group repositories and tablespace repositories.

This chapter contains these topics:

- Monitoring a File Group Repository

- Monitoring a Tablespace Repository

Note: The Streams tool in the Oracle Enterprise Manager Console is also an excellent way to monitor a Streams environment. See the online help for the Streams tool for more information.

See Also: Chapter 8, "Information Provisioning" Chapter 16, "Using Information Provisioning" Oracle Database Reference for information about the data dictionary views described in this chapter Oracle Streams Replication Administrator's Guide for information about monitoring a Streams replication environment

## Monitoring a File Group Repository

The queries in the following sections provide examples for monitoring a file group repository:

- Displaying General Information About the File Groups in a Database

- Displaying Information About File Group Versions

- Displaying Information About File Group Files

See Also: "File Group Repository" "Using a File Group Repository"

### Displaying General Information About the File Groups in a Database

The query in this section displays the following information for each file group in the local database:

- The file group owner

- The file group name

- Whether the files in a version of the file group are kept on disk if the version is purged

- The minimum number of versions of the file group allowed

- The maximum number of versions of the file group allowed

- The number of days to retain a file group version after it is created

Run the following query to display this information for the local database:

    COLUMN FILE_GROUP_OWNER HEADING 'File Group|Owner' FORMAT A10
    COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A10
    COLUMN KEEP_FILES HEADING 'Keep|Files?' FORMAT A10
    COLUMN MIN_VERSIONS HEADING 'Minimum|Number|of Versions' FORMAT 9999
    COLUMN MAX_VERSIONS HEADING 'Maximum|Number|of Versions' FORMAT 9999999999
    COLUMN RETENTION_DAYS HEADING 'Days to|Retain|a Version' FORMAT 9999999999.99

    SELECT FILE_GROUP_OWNER,
           FILE_GROUP_NAME,
           KEEP_FILES,
           MIN_VERSIONS,
           MAX_VERSIONS,
           RETENTION_DAYS
      FROM DBA_FILE_GROUPS;

Your output looks similar to the following:

    Minimum     Maximum        Days to
    File Group File Group Keep            Number      Number         Retain
    Owner      Name       Files?     of Versions of Versions      a Version
    ---------- ---------- ---------- ----------- ----------- --------------
    STRMADMIN  REPORTS    Y                    2  4294967295  4294967295.00

This output shows that the database has one file group with the following characteristics:

- The file group owner is `strmadmin`.

- The file group name is `reports`.

- The files in a version are kept on disk if a version is purged because the `"Keep Files?"` is `"Y"` for the file group.

- The minimum number of versions allowed is 2. If the file group automatically purges versions, then it will not purge a version if the purge would cause the total number of versions to drop below 2.

- The file group allows an infinite number of versions. The number 4294967295 means an infinite number of versions.

- The file group retains a version of an infinite number of days. The number 4294967295 means an infinite number of days.

### Displaying Information About File Group Versions

The query in this section displays the following information for each file group version in the local database:

- The owner of the file group that contains the version

- The name of the file group that contains the version

- The version name

- The version number

- The name of the user who created the version

- Comments for the version

Run the following query to display this information for the local database:

    COLUMN FILE_GROUP_OWNER HEADING 'File Group|Owner' FORMAT A10
    COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A10
    COLUMN VERSION_NAME HEADING 'Version Name' FORMAT A20
    COLUMN VERSION HEADING 'Version|Number' FORMAT 99999999
    COLUMN CREATOR HEADING 'Creator' FORMAT A10
    COLUMN COMMENTS HEADING 'Comments' FORMAT A14

    SELECT FILE_GROUP_OWNER,
           FILE_GROUP_NAME,
           VERSION_NAME,
           VERSION,
           CREATOR,
           COMMENTS
      FROM DBA_FILE_GROUP_VERSIONS;

Your output looks similar to the following:

    File Group File Group                        Version
    Owner      Name       Version Name            Number Creator    Comments
    ---------- ---------- -------------------- --------- ---------- --------------
    STRMADMIN  REPORTS    SALES_REPORTS_V1             1 STRMADMIN  Sales reports
                                                                    for week of 06
                                                                    -FEB-2005

    STRMADMIN  REPORTS    SALES_REPORTS_V2             2 STRMADMIN  Sales reports
                                                                    for week of 13
                                                                    -FEB-2005

### Displaying Information About File Group Files

The query in this section displays the following information about each file in a file group version in the local database:

- The owner of the file group that contains the file

- The name of the file group that contains the file

- The name of the version in the file group that contains the file

- The file name

- The directory object that contains the file

    COLUMN FILE_GROUP_OWNER HEADING 'File Group|Owner' FORMAT A10
    COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A10
    COLUMN VERSION_NAME HEADING 'Version Name' FORMAT A20
    COLUMN FILE_NAME HEADING 'File Name' FORMAT A15
    COLUMN FILE_DIRECTORY HEADING 'File Directory|Object' FORMAT A15

    SELECT FILE_GROUP_OWNER,
           FILE_GROUP_NAME,
           VERSION_NAME,
           FILE_NAME,
           FILE_DIRECTORY
      FROM DBA_FILE_GROUP_FILES;

Your output looks similar to the following:

    File Group File Group                                      File Directory
    Owner      Name       Version Name         File Name       Object
    ---------- ---------- -------------------- --------------- ---------------
    STRMADMIN  REPORTS    SALES_REPORTS_V1     book_sales.htm  SALES_REPORTS1
    STRMADMIN  REPORTS    SALES_REPORTS_V1     music_sales.htm SALES_REPORTS1
    STRMADMIN  REPORTS    SALES_REPORTS_V2     book_sales.htm  SALES_REPORTS2
    STRMADMIN  REPORTS    SALES_REPORTS_V2     music_sales.htm SALES_REPORTS2

Query the `DBA_DIRECTORIES` data dictionary view to determine the corresponding file system directory for a directory object.

## Monitoring a Tablespace Repository

The queries in the following sections provide examples for monitoring a tablespace repository:

- Displaying Information About the Tablespaces in a Tablespace Repository

- Displaying Information About the Tables in a Tablespace Repository

- Displaying Export Information About Versions in a Tablespace Repository

See Also: "Tablespace Repository" "Using a Tablespace Repository"

### Displaying Information About the Tablespaces in a Tablespace Repository

The query in this section displays the following information about each tablespace in the tablespace repository in the local database:

- The owner of the file group that contains the tablespace in the tablespace repository

- The name of the file group that contains the tablespace in the tablespace repository

- The name of the version that contains the tablespace

- The tablespace name

    COLUMN FILE_GROUP_OWNER HEADING 'File Group|Owner' FORMAT A15
    COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A15
    COLUMN VERSION_NAME HEADING 'Version Name' FORMAT A15
    COLUMN VERSION HEADING 'Version|Number' FORMAT 99999999
    COLUMN TABLESPACE_NAME HEADING 'Tablespace Name' FORMAT A15

    SELECT FILE_GROUP_OWNER,
           FILE_GROUP_NAME,
           VERSION_NAME,
           VERSION,
           TABLESPACE_NAME
      FROM DBA_FILE_GROUP_TABLESPACES;

Your output looks similar to the following:

    File Group      File Group                        Version
    Owner           Name            Version Name       Number Tablespace Name
    --------------- --------------- --------------- --------- ---------------
    STRMADMIN       SALES           V_Q1FY2005              1 SALES_TBS1
    STRMADMIN       SALES           V_Q1FY2005              1 SALES_TBS2
    STRMADMIN       SALES           V_Q2FY2005              3 SALES_TBS1
    STRMADMIN       SALES           V_Q2FY2005              3 SALES_TBS2
    STRMADMIN       SALES           V_Q1FY2005_R            4 SALES_TBS1
    STRMADMIN       SALES           V_Q1FY2005_R            4 SALES_TBS2
    STRMADMIN       SALES           V_Q2FY2005_R            5 SALES_TBS1
    STRMADMIN       SALES           V_Q2FY2005_R            5 SALES_TBS2

### Displaying Information About the Tables in a Tablespace Repository

The query in this section displays the following information about each table in the tablespace repository in the local database:

- The owner of the file group that contains the table in the tablespace repository

- The name of the file group that contains the table in the tablespace repository

- The name of the version that contains the table

- The table owner

- The table name

- The tablespace that contains the table

    COLUMN FILE_GROUP_OWNER HEADING 'File Group|Owner' FORMAT A10
    COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A10
    COLUMN VERSION_NAME HEADING 'Version Name' FORMAT A15
    COLUMN OWNER HEADING 'Table|Owner' FORMAT A10
    COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A15
    COLUMN TABLESPACE_NAME HEADING 'Tablespace Name' FORMAT A15

    SELECT FILE_GROUP_OWNER,
           FILE_GROUP_NAME,
           VERSION_NAME,
           OWNER,
           TABLE_NAME,
           TABLESPACE_NAME
      FROM DBA_FILE_GROUP_TABLES;

Your output looks similar to the following:

    File Group File Group                 Table
    Owner      Name       Version Name    Owner      Table Name      Tablespace Name
    ---------- ---------- --------------- ---------- --------------- ---------------
    STRMADMIN  SALES      V_Q1FY2005      SL         ORDERS          SALES_TBS1
    STRMADMIN  SALES      V_Q1FY2005      SL         ORDER_ITEMS     SALES_TBS1
    STRMADMIN  SALES      V_Q1FY2005      SL         CUSTOMERS       SALES_TBS2
    STRMADMIN  SALES      V_Q2FY2005      SL         ORDERS          SALES_TBS1
    STRMADMIN  SALES      V_Q2FY2005      SL         ORDER_ITEMS     SALES_TBS1
    STRMADMIN  SALES      V_Q2FY2005      SL         CUSTOMERS       SALES_TBS2
    STRMADMIN  SALES      V_Q1FY2005_R    SL         ORDERS          SALES_TBS1
    STRMADMIN  SALES      V_Q1FY2005_R    SL         ORDER_ITEMS     SALES_TBS1
    STRMADMIN  SALES      V_Q1FY2005_R    SL         CUSTOMERS       SALES_TBS2
    STRMADMIN  SALES      V_Q2FY2005_R    SL         ORDERS          SALES_TBS1
    STRMADMIN  SALES      V_Q2FY2005_R    SL         ORDER_ITEMS     SALES_TBS1
    STRMADMIN  SALES      V_Q2FY2005_R    SL         CUSTOMERS       SALES_TBS2

### Displaying Export Information About Versions in a Tablespace Repository

To display export information about the versions in the tablespace repository in the local database, query the `DBA_FILE_GROUP_EXPORT_INFO` data dictionary view. This view only displays information for versions that contain a valid Data Pump export dump file. The query in this section displays the following export information about each version in the local database:

- The name of the file group that contains the version

- The name of the version

- The export version of the export dump file. The export version corresponds to the version of Data Pump that performed the export.

- The platform on which the export was performed

- The date and time of the export

- The global name of the exporting database

    COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A10
    COLUMN VERSION_NAME HEADING 'Version Name' FORMAT A13
    COLUMN EXPORT_VERSION HEADING 'Export|Version' FORMAT A7
    COLUMN PLATFORM_NAME HEADING 'Export Platform' FORMAT A17
    COLUMN EXPORT_TIME HEADING 'Export Time' FORMAT A17
    COLUMN SOURCE_GLOBAL_NAME HEADING 'Export|Database' FORMAT A10

    SELECT FILE_GROUP_NAME,
           VERSION_NAME,
           EXPORT_VERSION,
           PLATFORM_NAME,
           TO_CHAR(EXPORT_TIME, 'HH24:MI:SS MM/DD/YY') EXPORT_TIME,
           SOURCE_GLOBAL_NAME
      FROM DBA_FILE_GROUP_EXPORT_INFO;

Your output looks similar to the following:

    File Group               Export                                      Export
    Name       Version Name  Version Export Platform   Export Time       Database
    ---------- ------------- ------- ----------------- ----------------- ----------
    SALES      V_Q1FY2005    10.2.0  Linux IA (32-bit) 12:23:52 03/08/05 INST1.NET
    SALES      V_Q2FY2005    10.2.0  Linux IA (32-bit) 12:27:37 03/08/05 INST1.NET
    SALES      V_Q1FY2005_R  10.2.0  Linux IA (32-bit) 12:39:50 03/08/05 INST2.NET
    SALES      V_Q2FY2005_R  10.2.0  Linux IA (32-bit) 12:46:04 03/08/05 INST2.NET